const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");

// 预收抵扣明细
router.post('/report/ysdk', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let buId = formData.buId?formData.buId:0;
    let excel = body.excel
    let sqls = `
       with bills as (
        select 1 isInit, buId, buName, '' subjectName,0 yusAdd,0 dikou,ye, '' billNo, '' billDate,N'期初余额' transTypeName from (
            select a.buId,co.name buName,sum( b.price2-b.price1) ye 
            from card a inner join card_info b on a.tenantId=b.tenantId and a.id=b.cardId
                and a.billState='accounted'
                inner join category ca on b.tenantId=ca.tenantId and b.subjectId=ca.id
                        left join contact co on a.tenantId=co.tenantId and a.buId=co.id
            left join invoice c on a.tenantId=c.tenantId and a.byIid=c.id
            where a.tenantId=${tenantId} 
              and b.subjectId in (select id from category where tenantId=${tenantId} and typeNumber='subject' and name=N'预收账款')
            and a.billDate <'${beginDate}' and (${buId}=0 or a.buId=${buId}) group by a.buId, co.name 
        ) a 
       union all 
        select 0 isInit, a.buId,co.name buName,ca.name subjectName,
               b.price2 yusAdd,
               b.price1 dikou,
               b.price2-b.price1 ye,
               iif(c.id is null,a.billNo,c.billNo) billNo ,
               iif(c.id is null,a.billDate,c.billDate) billDate ,
               iif(c.id is null,a.transTypeName,c.transTypeName) transTypeName 
        from card a inner join card_info b on a.tenantId=b.tenantId and a.id=b.cardId
            and a.billState='accounted'
            inner join category ca on b.tenantId=ca.tenantId and b.subjectId=ca.id
                    left join contact co on a.tenantId=co.tenantId and a.buId=co.id
        left join invoice c on a.tenantId=c.tenantId and a.byIid=c.id
        where a.tenantId=${tenantId} 
          and b.subjectId in (select id from category where tenantId=${tenantId} and typeNumber='subject' and name=N'预收账款')
        and a.billDate between '${beginDate}' and '${endDate}' and (${buId}=0 or a.buId=${buId}) 
    ) select buId, buName, subjectName, yusAdd, dikou, billNo, billDate, transTypeName ,ye from bills order by buName,isInit desc,billDate 
    `
    let ret = await db.query(sqls, {type: QueryTypes.SELECT})
    for (let i = 0; i < ret.length; i++) {
        if(i > 0) {
            ret[i].ye = tool.add(ret[i-1].ye,ret[i].ye)
        }
    }
    if(ret.length > 0) {
        if(ret[0].transTypeName !== '期初余额') {
            ret.unshift({
                buId: ret[0].buId,
                buName: ret[0].buName,
                transTypeName: '期初余额',
                ye:0
            })
        }
    }
    if(excel) {
        return tool.doExcelOut(ctx, ret)
    }
    else {
        return ctx.suc('', {
            total: ret.length,
            list: ret
        })
    }


})

module.exports = router;
